Selecting Specific Columns from Tables in MySQL – Complete Detailed Notes

Selecting specific columns is a fundamental MySQL skill that allows you to retrieve only the information required from a database table. Instead of displaying every column, you can use the SELECT statement to choose particular fields, improve query readability, reduce unnecessary data retrieval, and prepare results for reports and applications.

Course: JustAcademy MySQL Training Course

Course Demo: Register for MySQL Course Demo

1. Introduction to Selecting Specific Columns

A MySQL table contains rows and columns. Each column represents a particular type of information, such as a student's name, email address, age, or course name. When you need only some of these details, you can specify the required column names in a SELECT statement.

For example, a students table may contain student_id, student_name, email, age, course_name, and admission_date. If you only need student names and course names, you can retrieve those two columns without displaying the other fields.

Common reasons to select specific columns include:

• Displaying only relevant information in an application.
• Creating reports with selected fields.
• Reducing unnecessary data transfer.
• Making query results easier to read and understand.
• Avoiding the accidental exposure of unrelated or sensitive columns.
• Preparing data for analysis, filtering, sorting, and aggregation.

2. Learning Objectives

After completing these notes, you will be able to:

• Understand the purpose of the SELECT statement.
• Retrieve a single column from a table.
• Retrieve multiple specific columns.
• Use column aliases to rename output headings.
• Understand the difference between SELECT column names and SELECT *.
• Use expressions and functions in a SELECT list.
• Retrieve distinct values using DISTINCT.
• Combine column selection with WHERE, ORDER BY, and LIMIT.
• Understand common SELECT errors and their solutions.
• Apply specific-column selection in a practical MySQL project.

3. What Is the SELECT Statement?

The SELECT statement retrieves data from one or more tables, views, or other supported data sources. When you specify column names after SELECT, MySQL returns the requested columns in the order you specify them.

3.1 Basic Syntax

SELECT column_name
FROM table_name;

Explanation:

• SELECT specifies which columns or expressions should appear in the result.
• column_name is the name of the column to retrieve.
• FROM identifies the table or data source.
• table_name is the name of the table containing the required data.
• The semicolon marks the end of the SQL statement.

3.2 Example

SELECT student_name
FROM students;

This query returns the student_name column from the students table. It displays the names of all students represented by rows in that table, subject to any applicable view definition or query conditions.

4. Creating a Sample Table

Before practicing specific-column selection, create a sample database and table.

Step 1: Create a Database

CREATE DATABASE college_db;

Step 2: Select the Database

USE college_db;

Step 3: Create a Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    course_name VARCHAR(100),
    admission_date DATE,
    city VARCHAR(100)
);

Step 4: Insert Sample Records

INSERT INTO students
    (student_name, email, age, course_name, admission_date, city)
VALUES
    ('Aarav Sharma', '[email protected]', 22, 'MySQL', '2026-10-01', 'Mumbai'),
    ('Priya Verma', '[email protected]', 21, 'Python', '2026-10-02', 'Pune'),
    ('Rahul Mehta', '[email protected]', 24, 'Data Analytics', '2026-10-03', 'Delhi'),
    ('Simran Kaur', '[email protected]', 23, 'Web Development', '2026-10-04', 'Mumbai'),
    ('Neha Singh', '[email protected]', 20, 'MySQL', '2026-10-05', 'Nashik');

The table now contains student details that you can use to practice SELECT queries.

5. Selecting a Single Specific Column

To retrieve only one column, write its name after the SELECT keyword.

Example 1: Select Student Names

SELECT student_name
FROM students;

This returns the student_name column for all rows in the table.

Example 2: Select Email Addresses

SELECT email
FROM students;

This returns only email addresses. Other columns, such as student_id and age, are not included in the result.

Example 3: Select Course Names

SELECT course_name
FROM students;

If several students are enrolled in the same course, that course name can appear multiple times. SELECT does not remove duplicates automatically.

6. Selecting Multiple Specific Columns

You can retrieve multiple columns by separating their names with commas. The columns appear in the result in the order specified in the SELECT list.

Syntax

SELECT column1, column2, column3
FROM table_name;

Example 1: Select Names and Courses

SELECT student_name, course_name
FROM students;

This displays the student's name and course name for each row.

Example 2: Select Names, Ages, and Cities

SELECT student_name, age, city
FROM students;

This query returns three columns in the following order: student_name, age, and city.

Example 3: Select Student IDs and Email Addresses

SELECT student_id, email
FROM students;

This is useful when a report needs student identifiers and contact details but does not require course or admission information.

7. Understanding Column Order in SELECT

The order of column names in the SELECT statement determines the order of the result columns. It does not change the actual structure or order of columns in the table.

Example 1

SELECT student_name, age, city
FROM students;

The result columns appear as student_name, age, and city.

Example 2

SELECT city, student_name, age
FROM students;

The result columns now appear as city, student_name, and age. The stored data remains unchanged.

Important: Changing the SELECT list changes only the result set, not the table schema or stored records.

8. Selecting All Columns Using the Asterisk (*)

The asterisk (*) is used to retrieve all columns from a table.

Syntax

SELECT *
FROM students;

This query returns all columns in the students table. It is convenient when exploring a small table or inspecting sample data.

Difference Between SELECT * and Specific Columns

Best practice: Prefer explicitly listing columns in application queries and reports when you know which fields are required.

9. Using Column Aliases with AS

A column alias changes the heading displayed in the query result. It does not rename the actual table column.

Syntax

SELECT column_name AS alias_name
FROM table_name;

Example 1: Rename a Result Heading

SELECT student_name AS Name
FROM students;

The result heading displays Name instead of student_name.

Example 2: Alias Multiple Columns

SELECT
    student_name AS Student,
    course_name AS Course,
    city AS Location
FROM students;

This creates clearer headings for reports and exported results.

Example 3: Use an Alias Without AS

SELECT student_name Student
FROM students;

MySQL permits omitting AS in many column-alias expressions. Using AS is often clearer, especially for beginners and complex queries.

Example 4: Alias with Spaces

SELECT student_name AS 'Student Name'
FROM students;

An alias with spaces can be quoted. Backticks are also commonly used for identifier aliases.

10. Selecting Specific Columns with DISTINCT

The DISTINCT keyword removes duplicate rows from the selected result columns. It is useful when you want a list of unique course names, cities, or other repeated values.

Example 1: Find Unique Course Names

SELECT DISTINCT course_name
FROM students;

If multiple students are enrolled in MySQL, the course name appears only once in this result.

Example 2: Find Unique Cities

SELECT DISTINCT city
FROM students;

This returns the different city values present in the table. If a column contains NULL, DISTINCT can return a single NULL result for that value.

Example 3: Distinct Combinations of Two Columns

SELECT DISTINCT course_name, city
FROM students;

When multiple columns are selected with DISTINCT, MySQL removes duplicate combinations of those selected values. It does not independently make every individual column unique.

11. Selecting Specific Columns with WHERE

The WHERE clause filters rows according to a condition. You can combine it with specific-column selection to retrieve only the required fields from matching records.

Example 1: Select Names of Students Older Than 21

SELECT student_name, age
FROM students
WHERE age > 21;

This returns only student names and ages for rows where age is greater than 21.

Example 2: Select Students Enrolled in MySQL

SELECT student_name, course_name
FROM students
WHERE course_name = 'MySQL';

This returns the names and courses of students whose course_name equals MySQL.

Example 3: Select Students from Mumbai

SELECT student_name, city
FROM students
WHERE city = 'Mumbai';

This filters the rows to students whose city value matches Mumbai, subject to the column's comparison collation.

Example 4: Select Records with a Range

SELECT student_name, age
FROM students
WHERE age BETWEEN 20 AND 23;

BETWEEN includes both endpoints, so this query selects students whose ages range from 20 through 23.

12. Selecting Specific Columns with ORDER BY

The ORDER BY clause sorts the returned rows according to one or more columns. It can be combined with a SELECT list containing only the columns you want to display.

Example 1: Sort Students by Name

SELECT student_name, course_name
FROM students
ORDER BY student_name ASC;

ASC sorts values in ascending order. For text, the exact ordering depends on the applicable collation.

Example 2: Sort by Age from Highest to Lowest

SELECT student_name, age
FROM students
ORDER BY age DESC;

DESC sorts the rows in descending order.

Example 3: Sort by Multiple Columns

SELECT student_name, city, age
FROM students
ORDER BY city ASC, age DESC;

This sorts first by city in ascending order. Within each city, students are sorted by age in descending order.

13. Selecting Specific Columns with LIMIT

The LIMIT clause restricts the number of rows returned by a query. It is helpful when displaying a small result set, previewing data, or implementing pagination.

Example 1: Display the First Three Records

SELECT student_name, course_name
FROM students
LIMIT 3;

This returns at most three rows. Without ORDER BY, the rows returned should not be treated as having a guaranteed order.

Example 2: Display the Three Oldest Students

SELECT student_name, age
FROM students
ORDER BY age DESC
LIMIT 3;

This sorts students by age in descending order and then returns at most three rows.

Example 3: Skip Rows Using LIMIT

SELECT student_name, course_name
FROM students
ORDER BY student_id
LIMIT 2, 3;

The syntax LIMIT 2, 3 skips two rows and returns the next three rows. The equivalent clearer form is:

SELECT student_name, course_name
FROM students
ORDER BY student_id
LIMIT 3 OFFSET 2;

Using ORDER BY with a stable sorting key is recommended when predictable pagination is required.

14. Selecting Columns Using Expressions

The SELECT list can contain expressions as well as ordinary column names. Expressions allow you to calculate values or transform information in the result without changing the stored table data.

Example 1: Calculate a Value

SELECT student_name, age, age + 1 AS age_next_year
FROM students;

This displays each student's current age and a calculated value representing the age plus one.

Example 2: Combine Text Values

SELECT CONCAT(student_name, ' - ', course_name) AS student_course
FROM students;

CONCAT combines the student's name and course name into a single result column. In MySQL, CONCAT returns NULL if an argument is NULL.

Example 3: Display a Fixed Value

SELECT student_name, 'Enrolled' AS enrollment_status
FROM students;

This adds a calculated result column containing Enrolled for every returned row. It does not create or modify a column in the table.

15. Selecting Columns with Aggregate Functions

Aggregate functions summarize values across multiple rows. They are often used with SELECT to calculate totals, counts, averages, minimums, and maximums.

Example 1: Count Students

SELECT COUNT(*) AS total_students
FROM students;

COUNT(*) counts the rows in the result set.

Example 2: Calculate Average Age

SELECT AVG(age) AS average_age
FROM students;

AVG calculates the average of non-NULL age values.

Example 3: Find the Highest Age

SELECT MAX(age) AS highest_age
FROM students;

MAX returns the greatest non-NULL age value.

Example 4: Count Students in Each Course

SELECT course_name, COUNT(*) AS student_count
FROM students
GROUP BY course_name;

GROUP BY creates groups of rows with matching course_name values and calculates the number of rows in each group.

16. Selecting Columns from More Than One Table

When information is distributed across related tables, you can select specific columns from multiple tables using a JOIN. Table aliases help identify which table each column belongs to.

Step 1: Create a Courses Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

Step 2: Create a Student Enrollment Table

CREATE TABLE student_enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Step 3: Insert Sample Data

INSERT INTO courses (course_id, course_name)
VALUES
    (1, 'MySQL'),
    (2, 'Python'),
    (3, 'Data Analytics');

INSERT INTO student_enrollments (student_name, course_id)
VALUES
    ('Aarav Sharma', 1),
    ('Priya Verma', 2),
    ('Rahul Mehta', 3);

Step 4: Select Specific Columns Using INNER JOIN

SELECT
    se.student_name,
    c.course_name
FROM student_enrollments AS se
INNER JOIN courses AS c
    ON se.course_id = c.course_id;

This query returns the student's name from student_enrollments and the course name from courses. INNER JOIN returns rows where the join condition matches in both tables.

Why Use Table Aliases?

• se is an alias for student_enrollments.
• c is an alias for courses.
• Aliases make queries shorter and easier to read.
• Qualifying column names helps avoid ambiguity when multiple tables contain columns with the same name.

17. Selecting Specific Columns with LIKE

The LIKE operator filters rows based on text patterns. It is useful when searching for names, email addresses, or course names that match a particular pattern.

Example 1: Names Starting with A

SELECT student_name, email
FROM students
WHERE student_name LIKE 'A%';

The percent sign matches zero or more characters, so this query finds names that begin with A.

Example 2: Course Names Containing SQL

SELECT student_name, course_name
FROM students
WHERE course_name LIKE '%SQL%';

This pattern matches course names containing SQL. Whether text comparisons are case-sensitive depends on the collation used for the comparison.

18. Handling NULL Values in Selected Columns

A column may contain NULL values when the schema permits them. Selecting that column displays NULL for rows where the value is missing.

Example: Display Students with Missing Ages

SELECT student_name, age
FROM students
WHERE age IS NULL;

Example: Display Students with Known Ages

SELECT student_name, age
FROM students
WHERE age IS NOT NULL;

Do not use = NULL or <> NULL to test for NULL values. Use IS NULL or IS NOT NULL instead.

19. Selecting Columns from a Specific Database

You can specify the database name before the table name when you need to retrieve data without selecting the database first.

Example

SELECT student_name, course_name
FROM college_db.students;

This retrieves the specified columns from the students table in college_db.

To check the currently selected database, use:

SELECT DATABASE();

20. Common Errors and Their Solutions

21. Best Practices for Selecting Specific Columns

• List only the columns required for the task.
• Use clear aliases when result headings need to be more readable.
• Use table aliases and qualified column names in multi-table queries.
• Use DISTINCT only when removing duplicate result rows is necessary.
• Use WHERE to filter rows and ORDER BY to specify their display order.
• Use LIMIT to restrict the result size when appropriate.
• Avoid selecting sensitive columns unless they are needed and the user is authorized to access them.
• Use aggregate functions and GROUP BY for summary reports.
• Use EXPLAIN to inspect the execution plan when query performance needs investigation.
• Remember that selecting fewer columns can reduce unnecessary data transfer, but performance also depends on indexes, filters, joins, and the query execution plan.

22. Practical Project: Student Information Report

Build a simple report using specific-column selection and other SELECT clauses.

Task 1: Display Student Names and Courses

SELECT student_name, course_name
FROM students;

Task 2: Display Names and Cities with Friendly Headings

SELECT
    student_name AS 'Student Name',
    city AS 'Student City'
FROM students;

Task 3: Find Students from Mumbai

SELECT student_name, email, city
FROM students
WHERE city = 'Mumbai';

Task 4: Display Unique Courses

SELECT DISTINCT course_name
FROM students;

Task 5: Display the Three Oldest Students

SELECT student_name, age, course_name
FROM students
WHERE age IS NOT NULL
ORDER BY age DESC
LIMIT 3;

Task 6: Count Students in Each Course

SELECT
    course_name,
    COUNT(*) AS total_students
FROM students
GROUP BY course_name
ORDER BY total_students DESC;

Task 7: Display a Combined Student and Course Label

SELECT
    CONCAT(student_name, ' - ', course_name) AS student_course
FROM students;

Project outcome: You will learn to select specific columns, rename result headings, filter rows, remove duplicate combinations, sort results, limit output, and create summary reports.

23. Frequently Asked Questions (FAQs)

Q1. What does selecting specific columns mean in MySQL?

It means listing the exact column names required in a SELECT statement instead of retrieving all columns.

Q2. What is the syntax for selecting one column?

Use SELECT column_name FROM table_name;.

Q3. How do I select multiple columns?

Separate the column names with commas, for example SELECT student_name, age FROM students;.

Q4. What is the difference between SELECT * and SELECT column_name?

SELECT * returns all columns from the selected source, while explicitly listing columns returns only the requested fields.

Q5. Does selecting specific columns change the table structure?

No. SELECT retrieves data and does not modify the table structure or stored records.

Q6. What is a column alias?

A column alias changes the heading shown in the query result. It does not rename the underlying table column.

Q7. What does DISTINCT do?

DISTINCT removes duplicate result rows based on the complete set of selected expressions.

Q8. Can I combine SELECT with WHERE and ORDER BY?

Yes. WHERE filters the rows, while ORDER BY controls the order of the resulting rows.

Q9. How can I select columns from two tables?

Use a JOIN and qualify each column with its table name or alias when necessary.

Q10. Does selecting fewer columns always make a query faster?

Not always. Selecting fewer columns can reduce data transfer and processing, but performance also depends on filtering, joins, indexes, table size, and the execution plan.

24. Practice Questions

1. What is the purpose of the SELECT statement in MySQL?
2. Write a query to display only employee names from an employees table.
3. How can you select three specific columns from a table?
4. What is the difference between SELECT * and explicitly listing columns?
5. How can you change a column's output heading using AS?
6. What is the purpose of DISTINCT?
7. Write a query to display student names and ages for students older than 21.
8. How can you sort selected columns by age in descending order?
9. What does LIMIT do in a SELECT query?
10. How can you find rows where a selected column contains NULL?
11. How can you retrieve columns from two related tables using JOIN?
12. Why should application queries avoid retrieving unnecessary sensitive columns?

25. Quick Revision

• SELECT student_name FROM students; retrieves one column.
• SELECT student_name, age FROM students; retrieves multiple columns.
• SELECT * FROM students; retrieves all columns from the selected source.
• AS assigns an alias to a result column.
• DISTINCT removes duplicate result rows.
• WHERE filters rows according to a condition.
• ORDER BY sorts result rows.
• LIMIT restricts the number of returned rows.
• GROUP BY groups rows for aggregate calculations.
• JOIN retrieves related data from multiple tables.
• IS NULL and IS NOT NULL test for NULL values.
• SELECT retrieves data without changing the underlying table records.

26. Official Documentation and Further Learning

• MySQL Official Documentation: SELECT Statement
• MySQL Official Documentation: SELECT Optimization
• MySQL Official Documentation: Aggregate Functions
• MySQL Official Documentation: JOIN Clauses

Conclusion

Selecting specific columns is a core MySQL skill that helps you retrieve relevant information clearly and efficiently. By combining SELECT with aliases, DISTINCT, WHERE, ORDER BY, LIMIT, aggregate functions, and JOIN operations, you can create useful reports and application queries while avoiding unnecessary data retrieval.

Continue learning with the JustAcademy MySQL Training Course or register for a MySQL course demo.